﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlServerCe;
using ShoppingList.DTO;
using System.Data;

namespace ShoppingList.DAL
{
    public class DALContext : IDisposable
    {
        const string databaseName = "ShoppingList.sdf";

        public static DALContext Instance { get; private set; }

        public static void Open()
        {
            if (Instance == null) Instance = new DALContext();
        }

        public static void Close()
        {
            if (Instance != null) Instance.Dispose();
            Instance = null;
        }

        private List<Article> articles;
        private List<ShoppingItem> items;
        private Dictionary<int, Category> categories;

        private SqlCeConnection Connection;

        public void Dispose()
        {
            if (Connection != null) Connection.Dispose();
            GC.SuppressFinalize(this);
        }

        ~DALContext()
        {
            Dispose();
        }

        public DALContext()
            : base()
        {
            this.Connection = CreateConnection();
            Connection.Open();
            articles = new List<Article>();
            items = new List<ShoppingItem>();
            GetCategories();
            FinishShoppingTour(false);
        }

        public ICollection<Article> Articles { get { return articles; } }

        public ICollection<ShoppingItem> Items { get { return items; } }

        public IEnumerable<Category> Categories { get { return categories.Values; } }


        private static string GetConnectionPath(string databaseName)
        {
            return (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\" + databaseName);
        }

        private static SqlCeConnection CreateConnection()
        {
            string conStr = "Data Source =" + GetConnectionPath(databaseName);
            return new SqlCeConnection(conStr);
        }

        private void GetCategories()
        {
            categories = new Dictionary<int, Category>();
            using (SqlCeCommand cmd = Connection.CreateCommand())
            {
                cmd.CommandText = "select Id,Name from Category";
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        Category c = new Category(id, name);
                        categories.Add(id, c);
                    }
                }
            }
        }

        private void GetArticles()
        {
            articles.Clear();
            using (SqlCeCommand cmd = Connection.CreateCommand())
            {
                cmd.CommandText = "select Id,Name,CategoryId,IsFave from Product";
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        string name = reader.GetString(1);
                        int categoryId = reader.IsDBNull(2) ? 0 : reader.GetInt32(2);
                        bool isFave = reader.IsDBNull(3) ? false : reader.GetBoolean(3);
                        Category category = null;
                        categories.TryGetValue(categoryId, out category);

                        Article p = new Article(id, name, category, isFave);
                        p.PropertyChanged += new System.ComponentModel.PropertyChangedEventHandler(OnArticlePropertyChanged);
                        articles.Add(p);
                    }
                }
            }
        }

        void OnArticlePropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
        {
            Article p = sender as Article;
            if (p != null)
            {
                SaveArticle(p);
            }
        }

        private void GetItems()
        {
            items.Clear();
            using (SqlCeCommand cmd = Connection.CreateCommand())
            {
                cmd.CommandText = "select Id, IsScheduled, IsCompleted, [Name] from Item";
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = reader.GetInt32(0);
                        bool isScheduled = reader.GetBoolean(1);
                        bool isCompleted = reader.GetBoolean(2);
                        string name = reader.GetString(3);
                        ShoppingItem item = new ShoppingItem(id, isScheduled, isCompleted, name, null);
                        item.PropertyChanged += new System.ComponentModel.PropertyChangedEventHandler(OnShoppingItemPropertyChanged);
                        Items.Add(item);
                    }
                }
            }
        }

        void OnShoppingItemPropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
        {
            ShoppingItem item = sender as ShoppingItem;
            if (item != null)
            {
                SaveShoppingItem(item);
            }
        }

        public bool SaveShoppingItem(ShoppingItem item)
        {
            if (item == null) throw new ArgumentNullException("item");
            if (item.Id != 0) return UpdateShoppingItem(item); else return InsertShoppingItem(item);
        }

        public bool InsertShoppingItem(ShoppingItem item)
        {
            if (item == null) throw new ArgumentNullException("item");

            item.PropertyChanged -= OnShoppingItemPropertyChanged;
            item.PropertyChanged += OnShoppingItemPropertyChanged;
            try
            {
                using (var trans = Connection.BeginTransaction())
                {
                    using (SqlCeCommand cmd = Connection.CreateCommand())
                    {
                        cmd.CommandText = "insert into Item (Name,IsScheduled,IsCompleted,Rank) Values(@name,@isScheduled,@isCompleted,0)";
                        cmd.Parameters.AddWithValue("@isScheduled", item.IsScheduled);
                        cmd.Parameters.AddWithValue("@isCompleted", item.IsCompleted);
                        cmd.Parameters.AddWithValue("@name", item.Name);
                        cmd.ExecuteNonQuery();
                    }
                    using (SqlCeCommand command = new SqlCeCommand("select @@IDENTITY", Connection))
                    {
                        int id = (int)((decimal)command.ExecuteScalar());
                        item.id = id;
                    }
                    items.Add(item);
                    items.Sort(CompareItem);
                    trans.Commit();
                    return true;
                }
            }
            catch (Exception ex)
            {
                OnException("InsertShoppingItem", ex);
                return false;
            }
        }

        public bool UpdateShoppingItem(ShoppingItem item)
        {
            if (item == null) throw new ArgumentNullException("item");
            try
            {
                using (SqlCeCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandText = "update Item set IsScheduled=@isScheduled, IsCompleted=@isCompleted, Name=@name, Modified=@modified where Id=@id";
                    cmd.Parameters.AddWithValue("@isScheduled", item.IsScheduled);
                    cmd.Parameters.AddWithValue("@isCompleted", item.IsCompleted);
                    cmd.Parameters.AddWithValue("@name", item.Name);
                    cmd.Parameters.AddWithValue("@modified", DateTime.Now);
                    cmd.Parameters.AddWithValue("@id", item.Id);
                    cmd.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                OnException("UpdateShoppingItem", ex);
                return false;
            }
        }

        public bool SaveArticle(Article article)
        {
            if (article == null) throw new ArgumentNullException("article");
            if (article.Id == 0) return InsertArticle(article); else return UpdateArticle(article);
        }

        public bool UpdateArticle(Article article)
        {
            if (article == null) throw new ArgumentNullException("article");
            try
            {
                using (SqlCeCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandText = "update Product set Name=@name, IsFave=@isFave, CategoryId=@cid  where Id=@id";
                    cmd.Parameters.AddWithValue("@name", article.Name);
                    cmd.Parameters.AddWithValue("@id", article.Id);
                    cmd.Parameters.AddWithValue("@cid", article.Category != null ? article.Category.Id : 0);
                    cmd.Parameters.AddWithValue("@isFave", article.IsFave);
                    cmd.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                OnException("UpdateArticle", ex);
                return false;
            }
        }

        private static int CompareArticle(Article a, Article b)
        {
            return string.Compare(a.Name, b.Name, true);
        }

        private static int CompareItem(ShoppingItem a, ShoppingItem b)
        {
            return string.Compare(a.Name, b.Name, true);
        }

        public bool InsertArticle(Article article)
        {
            if (article == null) throw new ArgumentNullException("article");

            article.PropertyChanged -= OnArticlePropertyChanged;
            article.PropertyChanged += OnArticlePropertyChanged;

            try
            {
                using (var trans = Connection.BeginTransaction())
                {
                    using (SqlCeCommand cmd = Connection.CreateCommand())
                    {
                        cmd.CommandText = "insert into Product (Name,IsFave,CategoryId) values(@name,@isFave,@cid)";
                        cmd.Parameters.AddWithValue("@name", article.Name);
                        cmd.Parameters.AddWithValue("@cid", article.Category != null ? article.Category.Id : 0);
                        cmd.Parameters.AddWithValue("@isFave", article.IsFave);
                        cmd.ExecuteNonQuery();
                        using (SqlCeCommand command = new SqlCeCommand("select @@IDENTITY", Connection))
                        {
                            int id = (int)((decimal)command.ExecuteScalar());
                            article.id = id;
                        }
                    }
                    articles.Add(article);
                    articles.Sort(CompareArticle);
                    trans.Commit();
                    return true;
                }
            }
            catch (Exception ex)
            {
                OnException("InsertArticle", ex);
                return false;
            }
        }

        private void OnException(string methodName, Exception ex)
        {
            DatabaseException dbe = new DatabaseException(methodName, ex);
            if (DatabaseException != null)
            {
                DatabaseException(this, new DatabaseExceptionEventArgs(dbe));
            }
            else
            {
                throw dbe;
            }
        }

        public event EventHandler<DatabaseExceptionEventArgs> DatabaseException;

        public bool FinishShoppingTour(bool immediate)
        {
            DateTime date = DateTime.Now;
            date = date.AddHours(-4);
            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    if (!immediate)
                    {
                        command.CommandText = "delete from Item where Item.Modified is not null and Item.Modified<@date and IsCompleted=1";
                    }
                    else
                    {
                        command.CommandText = "delete from Item where  IsCompleted=1";
                    }
                    command.Parameters.AddWithValue("@date", date);
                    command.ExecuteNonQuery();
                }
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    if (!immediate)
                    {
                        command.CommandText = "update Item set IsScheduled=0 where Modified<@date and IsScheduled=1";
                    }
                    else
                    {
                        command.CommandText = "update Item set IsScheduled=0 where  IsScheduled=1";
                    }
                    command.Parameters.AddWithValue("@date", date);
                    command.ExecuteNonQuery();
                }
                GetItems();
                GetArticles();
                return true;
            }
            catch (Exception ex)
            {
                OnException("FinishShoppingTour", ex);
                return false;
            }
        }

        public bool DeleteArticle(Article p)
        {
            try
            {
                using (SqlCeCommand cmd = Connection.CreateCommand())
                {
                    cmd.CommandText = "delete from Product where Id=@id";
                    cmd.Parameters.AddWithValue("@id", p.Id);
                    cmd.ExecuteNonQuery();
                }
                return true;
            }
            catch (Exception ex)
            {
                OnException("DeleteArticle", ex);
                return false;
            }
        }

        public bool SetShoppingItems(IEnumerable<string> items)
        {
            try
            {
                using (var trans = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = "delete from Item";
                        command.ExecuteNonQuery();

                        command.CommandText = "insert into Item (Name, IsScheduled, IsCompleted, Rank) Values(@name,0,0,0)";
                        command.Parameters.Add("@name", SqlDbType.NVarChar);
                        command.Prepare();
                        foreach (var name in items)
                        {
                            command.Parameters[0].Value = name;
                            command.ExecuteNonQuery();
                        }
                    }
                    GetItems();
                    trans.Commit();
                    return true;
                }
            }
            catch (Exception ex)
            {
                OnException("SetShoppingItems", ex);
                return false;
            }
        }
    }
}
